const mysql = require('mysql2/promise');
const soapService = require('./soapService');

// 数据库配置
const dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'dataweb_db',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
};

// 创建数据库连接池
const pool = mysql.createPool(dbConfig);

// 解析 SOAP 响应
function parseSoapResponse(soapResult) {
    try {
        // 检查返回结果格式
        if (soapResult && soapResult.return && soapResult.return.$value) {
            // 尝试解析 SOAP 返回的 JSON 字符串
            let jsonStr = soapResult.return.$value;
            console.log('解析SOAP返回值 长度:', jsonStr.length);
            
            // 处理空数组的情况
            if (jsonStr === '[]') {
                console.log('SOAP返回了空数组');
                return { success: true, data: [] };
            }
            
            try {
                // 修复常见的JSON格式问题
                
                // 1. 修复带有非标准空格的JSON
                jsonStr = jsonStr.replace(/\s+/g, ' ');
                
                // 2. 修复键名中的特殊字符
                // 替换可能存在的非法字符，比如某些空格、控制字符等
                jsonStr = jsonStr.replace(/[\u0000-\u001F\u007F-\u009F]/g, '');
                
                // 3. 修复中文的不标准引号
                jsonStr = jsonStr.replace(/"/g, '"').replace(/"/g, '"');
                
                // 4. 修复键值对中间的空格问题
                jsonStr = jsonStr.replace(/([^\\])"\s*:\s*"/g, '$1":"');
                
                // 5. 处理可能存在的无效的转义字符
                jsonStr = jsonStr.replace(/\\([^"\\\/bfnrtu])/g, '$1');
                
                // 6. 尝试处理大数据截断问题
                const truncatedMatch = jsonStr.match(/(.*\}\]).*$/);
                if (truncatedMatch && truncatedMatch[1]) {
                    console.log('检测到可能的JSON截断，尝试使用有效部分');
                    jsonStr = truncatedMatch[1];
                }
                
                // 7. 修复特定字段的问题
                // 从错误位置上下文看，"型号"和"规格（数值）"附近可能有问题
                jsonStr = jsonStr.replace(/"型号":"([^"]*)","规格（数值）":([^0-9.-])/g, '"型号":"$1","规格（数值）":0');
                
                // 8. 修复销售含税类型和进货含税类型字段中可能的空格问题
                jsonStr = jsonStr.replace(/"销\s*售\s*含\s*税\s*类\s*型"/g, '"销售含税类型"');
                jsonStr = jsonStr.replace(/"进\s*货\s*含\s*税\s*类\s*型"/g, '"进货含税类型"');
                
                // 9. 全面替换字段中可能的空格
                const fieldNames = [
                    '日期', '营业点名称', '商品代码', '单价', '出库数量', '客商名称', '业务员名称', 
                    '单据类型', '来源单号', '商品名称', '型号', '规格（数值）', '品牌', '参数', 
                    '客户姓名', '电话', '手机', '地址', '手工单号', '税率', '含税价', '核算成本价', 
                    '销售含税类型', '进货含税类型', '开票数', '开票备注', '未扣点价', '项目单号', 
                    '出厂日期', '有效期（月）', '项目名称', '二级客户代码'
                ];
                
                fieldNames.forEach(field => {
                    const escapedField = field.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
                    const pattern = new RegExp(`"${escapedField.split('').join('\\s*')}"`, 'g');
                    jsonStr = jsonStr.replace(pattern, `"${field}"`);
                });
                
                // 10. 处理最常见的错误位置（根据错误信息）
                if (jsonStr.includes('80G/H355/450W/3.5*8"')) {
                    console.log('检测到特定的问题字符串，尝试修复');
                    jsonStr = jsonStr.replace(/80G\/H355\/450W\/3.5\*8""/g, '80G/H355/450W/3.5*8\\""');
                    jsonStr = jsonStr.replace(/80G\/H355\/450W\/3.5\*8"/g, '80G/H355/450W/3.5*8\\"');
                }
                
                // 修复特定位置的错误，尝试不同的修复方式
                try {
                    JSON.parse(jsonStr);
                } catch (firstAttemptError) {
                    console.log('第一次解析尝试失败，尝试更激进的修复方式');
                    
                    // 获取错误位置
                    const errorMatch = firstAttemptError.message.match(/position\s+(\d+)/);
                    if (errorMatch && errorMatch[1]) {
                        const errorPos = parseInt(errorMatch[1]);
                        const contextBefore = jsonStr.substring(Math.max(0, errorPos - 50), errorPos);
                        const contextAfter = jsonStr.substring(errorPos, Math.min(jsonStr.length, errorPos + 50));
                        console.log(`错误上下文: ${contextBefore} >>> ERROR HERE <<< ${contextAfter}`);
                        
                        // 修复特定位置问题
                        // 1. 尝试修复引号不匹配问题
                        let fixAttempt = jsonStr.substring(0, errorPos) + jsonStr.substring(errorPos + 1);
                        try {
                            JSON.parse(fixAttempt);
                            console.log('通过移除错误位置字符成功修复');
                            jsonStr = fixAttempt;
                        } catch (secondAttemptError) {
                            // 2. 尝试更激进的方式：找到当前对象的结束位置并截断
                            const lastGoodObjectEndPos = jsonStr.lastIndexOf('},{"', errorPos);
                            if (lastGoodObjectEndPos > 0) {
                                console.log(`尝试截断到最后一个已知良好的对象，位置: ${lastGoodObjectEndPos}`);
                                let nextObjectStartPos = jsonStr.indexOf('{"', lastGoodObjectEndPos + 1);
                                if (nextObjectStartPos > 0) {
                                    const truncatedStr = jsonStr.substring(0, lastGoodObjectEndPos + 1) + ',' + 
                                                        jsonStr.substring(nextObjectStartPos);
                                    try {
                                        JSON.parse(truncatedStr);
                                        console.log('通过截断到最后一个良好对象成功修复');
                                        jsonStr = truncatedStr;
                                    } catch (thirdAttemptError) {
                                        console.log('尝试对接不成功，返回到激进修复前的版本');
                                    }
                                }
                            }
                        }
                    }
                }
                
                // 记录修复后的JSON字符串前100个字符和后100个字符用于调试
                console.log('修复后的JSON字符串 开头:', jsonStr.substring(0, 100));
                console.log('修复后的JSON字符串 结尾:', jsonStr.substring(jsonStr.length - 100));
                
                const data = JSON.parse(jsonStr);
                return { success: true, data };
            } catch (parseError) {
                console.error('JSON解析失败:', parseError, '原始数据前500字符:', jsonStr.substring(0, 500));
                
                // 尝试分析具体错误位置
                const errorMatch = parseError.message.match(/position\s+(\d+)/);
                if (errorMatch && errorMatch[1]) {
                    const errorPos = parseInt(errorMatch[1]);
                    const errorContext = jsonStr.substring(
                        Math.max(0, errorPos - 20), 
                        Math.min(jsonStr.length, errorPos + 20)
                    );
                    console.error(`错误位置上下文: ===>${errorContext}<===`);
                }
                
                // 最后尝试方法：手动解析
                try {
                    console.log('尝试逐步手动解析JSON数组');
                    if (jsonStr.startsWith('[') && jsonStr.endsWith(']')) {
                        // 手动提取每个对象
                        const result = [];
                        let bracketCount = 0;
                        let objectStart = -1;
                        let inString = false;
                        let escapeNext = false;
                        
                        for (let i = 0; i < jsonStr.length; i++) {
                            const char = jsonStr[i];
                            
                            if (escapeNext) {
                                escapeNext = false;
                                continue;
                            }
                            
                            if (char === '\\') {
                                escapeNext = true;
                                continue;
                            }
                            
                            if (char === '"' && !escapeNext) {
                                inString = !inString;
                                continue;
                            }
                            
                            if (inString) continue;
                            
                            if (char === '{') {
                                if (bracketCount === 0) {
                                    objectStart = i;
                                }
                                bracketCount++;
                            } else if (char === '}') {
                                bracketCount--;
                                if (bracketCount === 0 && objectStart !== -1) {
                                    try {
                                        const objectStr = jsonStr.substring(objectStart, i + 1);
                                        const obj = JSON.parse(objectStr);
                                        result.push(obj);
                                        console.log(`成功解析第 ${result.length} 个对象`);
                                    } catch (objError) {
                                        console.error(`解析第 ${result.length + 1} 个对象失败:`, objError);
                                    }
                                    objectStart = -1;
                                }
                            }
                        }
                        
                        if (result.length > 0) {
                            console.log(`手动解析成功，共解析出 ${result.length} 个对象`);
                            return { success: true, data: result };
                        }
                    }
                } catch (manualParseError) {
                    console.error('手动解析失败:', manualParseError);
                }
                
                return { success: false, error: `JSON解析失败: ${parseError.message}` };
            }
        } else {
            // 直接返回原始结果
            console.log('SOAP返回格式不包含有效的$value, 使用原始结果');
            // 尝试返回空数组作为有效结果
            return { success: true, data: [] };
        }
    } catch (error) {
        console.error('解析 SOAP 响应失败:', error);
        return { success: false, error: '解析响应数据失败' };
    }
}

// 记录同步状态
async function updateSyncRecord(tableName, status, errorMessage = null) {
    try {
        const conn = await pool.getConnection();
        try {
            await conn.query(
                'INSERT INTO sync_record (table_name, last_sync_time, sync_status, error_message) VALUES (?, NOW(), ?, ?) ' +
                'ON DUPLICATE KEY UPDATE last_sync_time = NOW(), sync_status = ?, error_message = ?',
                [tableName, status, errorMessage, status, errorMessage]
            );
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error(`更新同步记录失败 [${tableName}]:`, error);
    }
}

// 1. 同步现金银行明细
async function syncCashBankDetail(startDate, endDate) {
    const tableName = 'cash_bank_detail';
    try {
        await updateSyncRecord(tableName, 'in_progress');
        
        // 调用 SOAP 服务获取数据
        const result = await soapService.getCashBankDetail(startDate, endDate);
        const parsedResult = parseSoapResponse(result);
        
        if (!parsedResult.success || !parsedResult.data || !Array.isArray(parsedResult.data)) {
            throw new Error('获取数据失败或数据格式不正确');
        }
        
        const data = parsedResult.data;
        const conn = await pool.getConnection();
        
        try {
            // 开始事务
            await conn.beginTransaction();
            
            // 清除该日期范围的旧数据
            await conn.query('DELETE FROM cash_bank_detail WHERE start_date = ? AND end_date = ?', [startDate, endDate]);
            
            // 插入新数据
            if (data.length > 0) {
                const values = data.map(item => [
                    item['收付方式'] || '',
                    item['名称'] || '',
                    item['货币'] || '',
                    parseFloat(item['期初余额'] || 0),
                    parseFloat(item['期末余额'] || 0),
                    parseFloat(item['本期收入'] || 0),
                    parseFloat(item['本期支出'] || 0),
                    parseFloat(item['本期调入'] || 0),
                    parseFloat(item['本期调出'] || 0),
                    parseFloat(item['本期发生额'] || 0),
                    item['上次代码'] || '',
                    parseInt(item['科目层次'] || 0),
                    startDate,
                    endDate
                ]);
                
                await conn.query(
                    'INSERT INTO cash_bank_detail (account_code, name, currency, beginning_balance, ending_balance, ' +
                    'period_income, period_expense, period_transfer_in, period_transfer_out, period_amount, ' +
                    'parent_code, account_level, start_date, end_date) VALUES ?',
                    [values]
                );
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            return { success: true, message: `成功同步 ${data.length} 条现金银行明细数据` };
        } catch (error) {
            // 回滚事务
            await conn.rollback();
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('同步现金银行明细失败:', error);
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    }
}

// 2. 同步仓库金额明细
async function syncStoreAmountDetail(startDate, endDate) {
    const tableName = 'store_amount_detail';
    let conn;
    
    try {
        console.log(`开始同步仓库金额明细数据，日期范围: ${startDate} 到 ${endDate}`);
        
        // 获取SOAP数据
        const soapResult = await soapService.getStoreAmountDetail(startDate, endDate);
        console.log('仓库金额明细 SOAP返回:', JSON.stringify(soapResult).substring(0, 500) + '...');
        
        if (!soapResult || !soapResult.return || !soapResult.return.$value) {
            console.error('SOAP返回结果格式不正确');
            return { success: false, error: 'SOAP返回结果格式不正确' };
        }
        
        try {
            // 尝试解析JSON数据
            const jsonStr = soapResult.return.$value;
            const data = JSON.parse(jsonStr);
            
            // 获取数据库连接
            conn = await pool.getConnection();
            await conn.beginTransaction();
            
            // 清除指定日期范围的旧数据
            await conn.query(
                'DELETE FROM store_amount_detail WHERE start_date = ? AND end_date = ?',
                [startDate, endDate]
            );
            
            // 获取当前表结构
            const [tableInfo] = await conn.query('SHOW CREATE TABLE store_amount_detail');
            const createTableSql = tableInfo[0]['Create Table'];
            console.log('当前表结构:', createTableSql);
            
            // 检查表结构是否需要更新
            const requiredColumns = [
                'internal_id', 'product_code', 'date', 'quantity', 'cost_price', 
                'discount_date', 'last_sale_date', 'department', 'category', 
                'unsold_days', 'start_date', 'end_date'
            ];
            
            // 获取当前列
            const [columns] = await conn.query('SHOW COLUMNS FROM store_amount_detail');
            const currentColumns = columns.map(col => col.Field);
            console.log('当前列:', currentColumns);
            
            // 检查是否需要修改表结构
            const missingColumns = requiredColumns.filter(col => !currentColumns.includes(col));
            if (missingColumns.length > 0) {
                console.log('需要添加的列:', missingColumns);
                
                // 如果表结构不匹配，先删除原表并创建新表
                await conn.query('DROP TABLE IF EXISTS store_amount_detail');
                
                // 创建新表
                await conn.query(`
                    CREATE TABLE store_amount_detail (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        internal_id INT,
                        product_code VARCHAR(50),
                        date DATE,
                        quantity DECIMAL(15,2),
                        cost_price DECIMAL(15,2),
                        discount_date DATE,
                        last_sale_date DATE,
                        department VARCHAR(50),
                        category VARCHAR(50),
                        unsold_days INT,
                        start_date DATE,
                        end_date DATE,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        INDEX idx_date_range (start_date, end_date),
                        INDEX idx_product (product_code)
                    )
                `);
                console.log('已创建新的表结构');
            }
            
            // 插入新数据
            if (data.length > 0) {
                // 根据实际的数据字段进行调整
                const values = data.map(item => [
                    parseInt(item['内部号'] || 0),
                    item['商品代码'] || '',
                    item['日期'] ? new Date(item['日期'].replace(/(\d+)\/(\d+)\/(\d+)/, '$3-$1-$2')) : null,
                    parseFloat(item['数量'] || 0),
                    parseFloat(item['成本价'] || 0),
                    item['折价日期'] && item['折价日期'] !== '1899/12/30' ? 
                        new Date(item['折价日期'].replace(/(\d+)\/(\d+)\/(\d+)/, '$3-$1-$2')) : null,
                    item['最后销售日期'] && item['最后销售日期'] !== '1899/12/30' ? 
                        new Date(item['最后销售日期'].replace(/(\d+)\/(\d+)\/(\d+)/, '$3-$1-$2')) : null,
                    item['部门'] || '',
                    item['种类'] || '',
                    parseInt(item['设定的滞销天数'] || 0),
                    startDate,
                    endDate
                ]);
                
                await conn.query(
                    'INSERT INTO store_amount_detail (internal_id, product_code, date, quantity, ' +
                    'cost_price, discount_date, last_sale_date, department, category, ' + 
                    'unsold_days, start_date, end_date) VALUES ?',
                    [values]
                );
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            
            return { 
                success: true, 
                message: `成功同步${data.length}条仓库金额明细数据`,
                count: data.length
            };
        } catch (jsonError) {
            console.error('解析仓库金额明细数据失败:', jsonError);
            if (conn) await conn.rollback();
            await updateSyncRecord(tableName, 'failed', jsonError.message);
            return { success: false, error: `解析仓库金额明细数据失败: ${jsonError.message}` };
        }
    } catch (error) {
        console.error('同步仓库金额明细数据失败:', error);
        if (conn) await conn.rollback();
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    } finally {
        if (conn) conn.release();
    }
}

// 3. 同步仓库金额
async function syncStoreAmount(startDate, endDate) {
    const tableName = 'store_amount';
    try {
        await updateSyncRecord(tableName, 'in_progress');
        
        // 调用 SOAP 服务获取数据
        console.log(`开始调用仓库金额SOAP服务，日期范围: ${startDate} 到 ${endDate}`);
        const result = await soapService.getStoreAmount(startDate, endDate);
        console.log('仓库金额SOAP返回:', JSON.stringify(result).substring(0, 200));
        
        const parsedResult = parseSoapResponse(result);
        console.log('解析后的结果:', JSON.stringify(parsedResult).substring(0, 200));
        
        // 检查解析是否成功
        if (!parsedResult.success) {
            console.error('SOAP数据解析失败:', parsedResult.error);
            throw new Error(`解析SOAP数据失败: ${parsedResult.error}`);
        }
        
        // 确保data是数组
        const data = Array.isArray(parsedResult.data) ? parsedResult.data : [];
        console.log(`仓库金额数据: 获取了 ${data.length} 条记录`);
        
        const conn = await pool.getConnection();
        
        try {
            // 开始事务
            await conn.beginTransaction();
            
            // 清除该日期范围的旧数据
            await conn.query('DELETE FROM store_amount WHERE start_date = ? AND end_date = ?', [startDate, endDate]);
            console.log(`已清除日期范围 ${startDate} 至 ${endDate} 的旧数据`);
            
            // 插入新数据
            if (data.length > 0) {
                // 根据实际的数据字段进行调整
                const values = data.map(item => [
                    item['仓库代码'] || '',
                    item['仓库名称'] || '',
                    parseFloat(item['期初金额'] || 0),
                    parseFloat(item['采购金额'] || 0),
                    parseFloat(item['销售金额'] || 0),
                    parseFloat(item['调入金额'] || 0),
                    parseFloat(item['调出金额'] || 0),
                    parseFloat(item['期末金额'] || 0),
                    startDate,
                    endDate
                ]);
                
                await conn.query(
                    'INSERT INTO store_amount (store_code, store_name, beginning_amount, purchase_amount, ' +
                    'sales_amount, transfer_in_amount, transfer_out_amount, ending_amount, ' +
                    'start_date, end_date) VALUES ?',
                    [values]
                );
                console.log(`成功插入 ${values.length} 条数据`);
            } else {
                console.log('没有数据需要插入');
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            return { 
                success: true, 
                message: `成功同步 ${data.length} 条仓库金额数据`,
                count: data.length
            };
        } catch (error) {
            // 回滚事务
            console.error('数据库操作失败:', error);
            await conn.rollback();
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('同步仓库金额失败:', error);
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    }
}

// 4. 同步收入金额
async function syncIncomeAmount(startDate, endDate) {
    const tableName = 'income_amount';
    try {
        await updateSyncRecord(tableName, 'in_progress');
        
        // 调用 SOAP 服务获取数据
        const result = await soapService.getIncomeAmount(startDate, endDate);
        const parsedResult = parseSoapResponse(result);
        
        if (!parsedResult.success || !parsedResult.data || !Array.isArray(parsedResult.data)) {
            throw new Error('获取数据失败或数据格式不正确');
        }
        
        const data = parsedResult.data;
        const conn = await pool.getConnection();
        
        try {
            // 开始事务
            await conn.beginTransaction();
            
            // 清除该日期范围的旧数据
            await conn.query('DELETE FROM income_amount WHERE start_date = ? AND end_date = ?', [startDate, endDate]);
            
            // 插入新数据
            if (data.length > 0) {
                // 根据实际的数据字段进行调整
                const values = data.map(item => [
                    item['科目代码'] || '',
                    item['科目名称'] || '',
                    parseFloat(item['金额'] || 0),
                    parseFloat(item['占比'] || 0),
                    startDate,
                    endDate
                ]);
                
                await conn.query(
                    'INSERT INTO income_amount (account_code, account_name, amount, percent, start_date, end_date) VALUES ?',
                    [values]
                );
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            return { success: true, message: `成功同步 ${data.length} 条收入金额数据` };
        } catch (error) {
            // 回滚事务
            await conn.rollback();
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('同步收入金额失败:', error);
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    }
}

// 5. 同步费用金额
async function syncFeeAmount(startDate, endDate) {
    const tableName = 'fee_amount';
    let conn;
    
    try {
        console.log(`开始同步费用金额数据，日期范围: ${startDate} 到 ${endDate}`);
        
        // 获取SOAP数据
        const soapResult = await soapService.getFeeAmount(startDate, endDate);
        console.log('费用金额 SOAP返回:', JSON.stringify(soapResult).substring(0, 500) + '...');
        
        if (!soapResult || !soapResult.return || !soapResult.return.$value) {
            console.error('SOAP返回结果格式不正确');
            return { success: false, error: 'SOAP返回结果格式不正确' };
        }
        
        try {
            // 尝试解析JSON数据
            const jsonStr = soapResult.return.$value;
            const data = JSON.parse(jsonStr);
            
            // 获取数据库连接
            conn = await pool.getConnection();
            await conn.beginTransaction();
            
            // 清除指定日期范围的旧数据
            await conn.query(
                'DELETE FROM fee_amount WHERE start_date = ? AND end_date = ?',
                [startDate, endDate]
            );
            
            // 检查表结构
            const [columns] = await conn.query('SHOW COLUMNS FROM fee_amount');
            const columnNames = columns.map(col => col.Field);
            console.log('费用金额表当前列:', columnNames);
            
            // 检查是否有level列，如果没有尝试添加
            const hasLevelColumn = columnNames.includes('level');
            const hasAccountLevelColumn = columnNames.includes('account_level');
            
            if (!hasLevelColumn && !hasAccountLevelColumn) {
                try {
                    // 尝试添加account_level列
                    await conn.query('ALTER TABLE fee_amount ADD COLUMN account_level INT');
                    console.log('已添加account_level列到fee_amount表');
                } catch (alterError) {
                    console.error('添加account_level列失败:', alterError);
                    // 继续执行，但不会插入level值
                }
            }
            
            // 插入新数据
            if (data.length > 0) {
                // 根据实际的数据字段进行调整
                let values;
                
                if (hasLevelColumn) {
                    // 使用level列
                    values = data.map(item => [
                        item['代码'] || '',
                        item['名称'] || '',
                        parseFloat(item['金额'] || 0),
                        parseInt(item['科目层次'] || 0),
                        item['上层代码'] || '',
                        startDate,
                        endDate
                    ]);
                    
                    await conn.query(
                        'INSERT INTO fee_amount (account_code, account_name, amount, level, parent_code, start_date, end_date) VALUES ?',
                        [values]
                    );
                } else if (hasAccountLevelColumn) {
                    // 使用account_level列
                    values = data.map(item => [
                        item['代码'] || '',
                        item['名称'] || '',
                        parseFloat(item['金额'] || 0),
                        parseInt(item['科目层次'] || 0),
                        item['上层代码'] || '',
                        startDate,
                        endDate
                    ]);
                    
                    await conn.query(
                        'INSERT INTO fee_amount (account_code, account_name, amount, account_level, parent_code, start_date, end_date) VALUES ?',
                        [values]
                    );
                } else {
                    // 完全不使用level字段
                    values = data.map(item => [
                        item['代码'] || '',
                        item['名称'] || '',
                        parseFloat(item['金额'] || 0),
                        item['上层代码'] || '',
                        startDate,
                        endDate
                    ]);
                    
                    await conn.query(
                        'INSERT INTO fee_amount (account_code, account_name, amount, parent_code, start_date, end_date) VALUES ?',
                        [values]
                    );
                }
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            
            return { 
                success: true, 
                message: `成功同步${data.length}条费用金额数据`,
                count: data.length
            };
        } catch (jsonError) {
            console.error('解析费用金额数据失败:', jsonError);
            if (conn) await conn.rollback();
            await updateSyncRecord(tableName, 'failed', jsonError.message);
            return { success: false, error: `解析费用金额数据失败: ${jsonError.message}` };
        }
    } catch (error) {
        console.error('同步费用金额数据失败:', error);
        if (conn) await conn.rollback();
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    } finally {
        if (conn) conn.release();
    }
}

// 6. 同步公司盈利
async function syncCompanyProfit(startDate, endDate, company = '', sales = '', trader = '', minNo = '', no = '') {
    const tableName = 'company_profit';
    try {
        await updateSyncRecord(tableName, 'in_progress');
        
        // 调用 SOAP 服务获取数据
        const result = await soapService.getCompanyProfit(startDate, endDate, company, sales, trader, minNo, no);
        const parsedResult = parseSoapResponse(result);
        
        if (!parsedResult.success || !parsedResult.data || !Array.isArray(parsedResult.data)) {
            throw new Error('获取数据失败或数据格式不正确');
        }
        
        const data = parsedResult.data;
        const conn = await pool.getConnection();
        
        try {
            // 开始事务
            await conn.beginTransaction();
            
            // 清除该日期范围的旧数据
            const deleteParams = [startDate, endDate];
            let deleteQuery = 'DELETE FROM company_profit WHERE start_date = ? AND end_date = ?';
            
            if (company) {
                deleteQuery += ' AND company = ?';
                deleteParams.push(company);
            }
            if (sales) {
                deleteQuery += ' AND sales = ?';
                deleteParams.push(sales);
            }
            if (trader) {
                deleteQuery += ' AND trader = ?';
                deleteParams.push(trader);
            }
            if (no) {
                deleteQuery += ' AND order_no = ?';
                deleteParams.push(no);
            }
            
            await conn.query(deleteQuery, deleteParams);
            
            // 插入新数据
            if (data.length > 0) {
                // 根据实际的数据字段进行调整
                const values = data.map(item => [
                    item['公司'] || '',
                    item['销售'] || '',
                    item['交易员'] || '',
                    item['订单号'] || '',
                    parseFloat(item['销售金额'] || 0),
                    parseFloat(item['成本金额'] || 0),
                    parseFloat(item['利润金额'] || 0),
                    parseFloat(item['利润率'] || 0),
                    startDate,
                    endDate
                ]);
                
                await conn.query(
                    'INSERT INTO company_profit (company, sales, trader, order_no, sales_amount, cost_amount, ' +
                    'profit_amount, profit_rate, start_date, end_date) VALUES ?',
                    [values]
                );
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            return { success: true, message: `成功同步 ${data.length} 条公司盈利数据` };
        } catch (error) {
            // 回滚事务
            await conn.rollback();
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('同步公司盈利失败:', error);
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    }
}

// 7. 同步盈利明细
async function syncProfitDetail(startDate, endDate, company = '', sales = '', trader = '', minNo = '', no = '') {
    const tableName = 'profit_detail';
    try {
        await updateSyncRecord(tableName, 'in_progress');
        
        // 调用 SOAP 服务获取数据
        const result = await soapService.getProfitDetail(startDate, endDate, company, sales, trader, minNo, no);
        const parsedResult = parseSoapResponse(result);
        
        if (!parsedResult.success || !parsedResult.data || !Array.isArray(parsedResult.data)) {
            throw new Error('获取数据失败或数据格式不正确');
        }
        
        const data = parsedResult.data;
        const conn = await pool.getConnection();
        
        try {
            // 开始事务
            await conn.beginTransaction();
            
            // 清除该日期范围的旧数据
            const deleteParams = [startDate, endDate];
            let deleteQuery = 'DELETE FROM profit_detail WHERE start_date = ? AND end_date = ?';
            
            if (company) {
                deleteQuery += ' AND company = ?';
                deleteParams.push(company);
            }
            if (sales) {
                deleteQuery += ' AND sales = ?';
                deleteParams.push(sales);
            }
            if (trader) {
                deleteQuery += ' AND trader = ?';
                deleteParams.push(trader);
            }
            if (no) {
                deleteQuery += ' AND order_no = ?';
                deleteParams.push(no);
            }
            
            await conn.query(deleteQuery, deleteParams);
            
            // 插入新数据
            if (data.length > 0) {
                // 根据实际的数据字段进行调整
                const values = data.map(item => [
                    item['公司'] || '',
                    item['销售'] || '',
                    item['交易员'] || '',
                    item['订单号'] || '',
                    item['产品代码'] || '',
                    item['产品名称'] || '',
                    parseFloat(item['数量'] || 0),
                    parseFloat(item['销售价格'] || 0),
                    parseFloat(item['销售金额'] || 0),
                    parseFloat(item['成本价格'] || 0),
                    parseFloat(item['成本金额'] || 0),
                    parseFloat(item['利润金额'] || 0),
                    parseFloat(item['利润率'] || 0),
                    item['交易日期'] ? new Date(item['交易日期']) : null,
                    startDate,
                    endDate
                ]);
                
                await conn.query(
                    'INSERT INTO profit_detail (company, sales, trader, order_no, product_code, product_name, ' +
                    'quantity, sales_price, sales_amount, cost_price, cost_amount, profit_amount, profit_rate, ' +
                    'transaction_date, start_date, end_date) VALUES ?',
                    [values]
                );
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            return { success: true, message: `成功同步 ${data.length} 条盈利明细数据` };
        } catch (error) {
            // 回滚事务
            await conn.rollback();
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('同步盈利明细失败:', error);
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    }
}

// 8. 同步销售明细
async function syncSellDetail(startDate, endDate, company = '', sales = '', trader = '', minNo = '', no = '') {
    const tableName = 'sell_detail';
    try {
        await updateSyncRecord(tableName, 'in_progress');
        
        // 调用 SOAP 服务获取数据
        const result = await soapService.getSellDetail(startDate, endDate, company, sales, trader, minNo, no);
        const parsedResult = parseSoapResponse(result);
        
        if (!parsedResult.success || !parsedResult.data || !Array.isArray(parsedResult.data)) {
            throw new Error('获取数据失败或数据格式不正确');
        }
        
        const data = parsedResult.data;
        const conn = await pool.getConnection();
        
        try {
            // 开始事务
            await conn.beginTransaction();
            
            // 清除该日期范围的旧数据
            const deleteParams = [startDate, endDate];
            let deleteQuery = 'DELETE FROM sell_detail WHERE start_date = ? AND end_date = ?';
            
            if (company) {
                deleteQuery += ' AND company = ?';
                deleteParams.push(company);
            }
            if (sales) {
                deleteQuery += ' AND sales = ?';
                deleteParams.push(sales);
            }
            if (trader) {
                deleteQuery += ' AND trader = ?';
                deleteParams.push(trader);
            }
            if (no) {
                deleteQuery += ' AND order_no = ?';
                deleteParams.push(no);
            }
            
            await conn.query(deleteQuery, deleteParams);
            
            // 获取当前表结构
            const [tableInfo] = await conn.query('SHOW CREATE TABLE sell_detail');
            const createTableSql = tableInfo[0]['Create Table'];
            console.log('当前表结构:', createTableSql);
            
            // 获取当前列
            const [columns] = await conn.query('SHOW COLUMNS FROM sell_detail');
            const currentColumns = columns.map(col => col.Field);
            console.log('当前列:', currentColumns);
            
            // 确保表包含所有必要字段
            const requiredColumns = [
                'date', 'branch_name', 'product_code', 'unit_price', 'output_quantity',
                'customer_code', 'salesman_name', 'document_type', 'source_no',
                'product_name', 'model', 'spec', 'brand', 'parameter', 'customer_name',
                'phone', 'mobile', 'address', 'manual_no', 'tax_rate', 'tax_included_price',
                'cost_price', 'sales_tax_type', 'purchase_tax_type', 'invoice_count',
                'invoice_remark', 'undiscounted_price', 'project_no', 'production_date',
                'valid_months', 'project_name', 'secondary_customer_code',
                'start_date', 'end_date'
            ];
            
            // 检查是否需要修改表结构
            const missingColumns = requiredColumns.filter(col => !currentColumns.includes(col));
            if (missingColumns.length > 0) {
                console.log('需要添加的列:', missingColumns);
                
                // 添加缺失的列
                for (const column of missingColumns) {
                    try {
                        await conn.query(`ALTER TABLE sell_detail ADD COLUMN ${column} VARCHAR(255)`);
                        console.log(`已添加列: ${column}`);
                    } catch (alterError) {
                        console.error(`添加列 ${column} 失败:`, alterError);
                    }
                }
            }
            
            // 插入新数据
            if (data.length > 0) {
                console.log('处理销售明细数据示例:', JSON.stringify(data[0]));
                
                // 根据实际的数据字段进行调整
                const values = data.map(item => {
                    // 日期格式转换
                    let transactionDate = null;
                    if (item['日期']) {
                        try {
                            // 处理多种可能的日期格式
                            if (item['日期'].includes('/')) {
                                // 处理类似 "2025/2/6" 格式
                                transactionDate = item['日期'] !== '1899/12/30' ? 
                                    new Date(item['日期'].replace(/(\d+)\/(\d+)\/(\d+)/, '$3-$1-$2')) : null;
                            } else {
                                transactionDate = new Date(item['日期']);
                            }
                        } catch (dateError) {
                            console.error('日期转换错误:', dateError, '原始日期:', item['日期']);
                        }
                    }
                    
                    // 处理生产日期格式
                    let productionDate = null;
                    if (item['出厂日期']) {
                        try {
                            if (item['出厂日期'].includes('/')) {
                                productionDate = item['出厂日期'] !== '1899/12/30' ? 
                                    new Date(item['出厂日期'].replace(/(\d+)\/(\d+)\/(\d+)/, '$3-$1-$2')) : null;
                            } else {
                                productionDate = new Date(item['出厂日期']);
                            }
                        } catch (dateError) {
                            console.error('生产日期转换错误:', dateError, '原始日期:', item['出厂日期']);
                        }
                    }
                    
                    return [
                        transactionDate,
                        item['营业点名称'] || '',
                        item['商品代码'] || '',
                        parseFloat(item['单价'] || 0),
                        parseFloat(item['出库数量'] || 0),
                        item['客商名称'] || '',
                        item['业务员名称'] || '',
                        item['单据类型'] || '',
                        item['来源单号'] || '',
                        item['商品名称'] || '',
                        item['型号'] || '',
                        item['规格（数值）'] || 0,
                        item['品牌'] || '',
                        item['参数'] || '',
                        item['客户姓名'] || '',
                        item['电话'] || '',
                        item['手机'] || '',
                        item['地址'] || '',
                        item['手工单号'] || '',
                        parseFloat(item['税率'] || 0),
                        parseFloat(item['含税价'] || 0),
                        parseFloat(item['核算成本价'] || 0),
                        item['销售含税类型'] || '',
                        item['进货含税类型'] || '',
                        parseFloat(item['开票数'] || 0),
                        item['开票备注'] || '',
                        parseFloat(item['未扣点价'] || 0),
                        item['项目单号'] || '',
                        productionDate,
                        parseFloat(item['有效期（月）'] || 0),
                        item['项目名称'] || '',
                        item['二级客户代码'] || '',
                        startDate,
                        endDate
                    ];
                });
                
                // 调试信息
                console.log(`准备插入 ${values.length} 条销售明细记录`);
                if (values.length > 0) {
                    console.log('第一条记录样例:', values[0]);
                }
                
                const placeholders = requiredColumns.map(() => '?').join(', ');
                
                await conn.query(
                    `INSERT INTO sell_detail (${requiredColumns.join(', ')}) VALUES ?`,
                    [values]
                );
            }
            
            // 提交事务
            await conn.commit();
            await updateSyncRecord(tableName, 'success');
            return { success: true, message: `成功同步 ${data.length} 条销售明细数据` };
        } catch (error) {
            // 回滚事务
            await conn.rollback();
            throw error;
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('同步销售明细失败:', error);
        await updateSyncRecord(tableName, 'failed', error.message);
        return { success: false, error: error.message };
    }
}

// 获取同步状态
async function getSyncStatus() {
    try {
        const conn = await pool.getConnection();
        try {
            const [rows] = await conn.query('SELECT * FROM sync_record ORDER BY table_name');
            return { success: true, data: rows };
        } finally {
            conn.release();
        }
    } catch (error) {
        console.error('获取同步状态失败:', error);
        return { success: false, error: error.message };
    }
}

// 同步所有数据
async function syncAllData(startDate, endDate) {
    try {
        const results = {
            cashBankDetail: await syncCashBankDetail(startDate, endDate),
            storeAmountDetail: await syncStoreAmountDetail(startDate, endDate),
            storeAmount: await syncStoreAmount(startDate, endDate),
            incomeAmount: await syncIncomeAmount(startDate, endDate),
            feeAmount: await syncFeeAmount(startDate, endDate),
            companyProfit: await syncCompanyProfit(startDate, endDate),
            profitDetail: await syncProfitDetail(startDate, endDate),
            sellDetail: await syncSellDetail(startDate, endDate)
        };
        
        return {
            success: Object.values(results).every(result => result.success),
            results
        };
    } catch (error) {
        console.error('同步所有数据失败:', error);
        return { success: false, error: error.message };
    }
}

// 导出模块
module.exports = {
    syncCashBankDetail,
    syncStoreAmountDetail,
    syncStoreAmount,
    syncIncomeAmount,
    syncFeeAmount,
    syncCompanyProfit,
    syncProfitDetail,
    syncSellDetail,
    syncAllData,
    getSyncStatus
}; 